﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Threading;

namespace Library
{
    public class clKhachhang
    {
        private SqlConnection sqlcon;
        private SqlCommand sqlcmd;
        public static SqlConnection GetConnection()
        {
            string strConnect = ConfigurationManager.ConnectionStrings["StrConnectString"].ToString();
            SqlConnection sqlcon = new SqlConnection(strConnect);
            return sqlcon;
        }

        public DataTable getdata(Int16 type)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_danhsach", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@type", SqlDbType.SmallInt).Value = type;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int addnew(itkhachhang item) // ki hop dong
        {
            Int16 rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_dangki", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;

                    sqlcmd.Parameters.Add("@makh", SqlDbType.NVarChar, 25).Value = item.maCu;
                    sqlcmd.Parameters.Add("@tenKH", SqlDbType.NVarChar, 150).Value = item.tenKH;
                    sqlcmd.Parameters.Add("@tenTK", SqlDbType.NVarChar, 150).Value = item.tenTK;
                    sqlcmd.Parameters.Add("@ngaysinh", SqlDbType.NVarChar, 50).Value = item.ngaysinh;
                    sqlcmd.Parameters.Add("@soCMND", SqlDbType.NVarChar, 15).Value = item.soCMND;
                    sqlcmd.Parameters.Add("@soDT", SqlDbType.NVarChar, 25).Value = item.soDT;
                    sqlcmd.Parameters.Add("@diachi", SqlDbType.NVarChar, 250).Value = item.diachi;
                    sqlcmd.Parameters.Add("@idTP", SqlDbType.SmallInt).Value = item.idTP;
                    sqlcmd.Parameters.Add("@TenTP", SqlDbType.NVarChar, 75).Value = item.TenTP;
                    sqlcmd.Parameters.Add("@idQH", SqlDbType.SmallInt).Value = item.idQH;
                    sqlcmd.Parameters.Add("@tenQH", SqlDbType.NVarChar, 75).Value = item.tenQH;
                    sqlcmd.Parameters.Add("@idPX", SqlDbType.SmallInt).Value = item.idPX;
                    sqlcmd.Parameters.Add("@tenPX", SqlDbType.NVarChar, 75).Value = item.tenPX;
                    sqlcmd.Parameters.Add("@idTo", SqlDbType.SmallInt).Value = item.idTo;
                    sqlcmd.Parameters.Add("@tenTo", SqlDbType.NVarChar, 75).Value = item.tenTo;
                    sqlcmd.Parameters.Add("@idDuong", SqlDbType.SmallInt).Value = item.idDuong;
                    sqlcmd.Parameters.Add("@tenDuong", SqlDbType.NVarChar, 150).Value = item.tenDuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = item.idPho;
                    sqlcmd.Parameters.Add("@tenPho", SqlDbType.NVarChar, 150).Value = item.tenPho;
                    sqlcmd.Parameters.Add("@loaiKH", SqlDbType.SmallInt).Value = item.loaiKH;
                    sqlcmd.Parameters.Add("@tenLKH", SqlDbType.NVarChar, 150).Value = item.tenLKH;
                    sqlcmd.Parameters.Add("@idMDSD", SqlDbType.SmallInt).Value = item.idMDSD;
                    sqlcmd.Parameters.Add("@tenMDSD", SqlDbType.NVarChar, 150).Value = item.tenMDSD;
                    sqlcmd.Parameters.Add("@sohoSD", SqlDbType.SmallInt).Value = item.sohoSD;
                    sqlcmd.Parameters.Add("@sokhauSD", SqlDbType.SmallInt).Value = item.sokhauSD;
                    sqlcmd.Parameters.Add("@ngayDK", SqlDbType.BigInt).Value = item.ngayDK;
                    sqlcmd.Parameters.Add("@trangthai", SqlDbType.SmallInt).Value = item.trangthai;
                    sqlcmd.Parameters.Add("@klttdukien", SqlDbType.Int).Value = item.KLTTdukien;
                    sqlcmd.Parameters.Add("@motakh", SqlDbType.NVarChar, 4000).Value = item.mota;
                    sqlcmd.Parameters.Add("@masothue", SqlDbType.NVarChar, 25).Value = item.maSoThue;
                    sqlcmd.ExecuteNonQuery();
                    sqlcmd.Dispose();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcon.Dispose(); sqlcmd.Dispose(); }
            return rtn;
        }

        public DataTable detail(Int64 id)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_thongtin", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int delete(Int64 id)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_xoa", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public int capnhatdangki(Int64 id,itkhachhang item)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_dangki_capnhat", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;

                    sqlcmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id;
                    sqlcmd.Parameters.Add("@makh", SqlDbType.NVarChar, 25).Value = item.maCu;
                    sqlcmd.Parameters.Add("@tenKH", SqlDbType.NVarChar, 150).Value = item.tenKH;
                    sqlcmd.Parameters.Add("@tenTK", SqlDbType.NVarChar, 150).Value = item.tenTK;
                    sqlcmd.Parameters.Add("@ngaysinh", SqlDbType.NVarChar, 50).Value = item.ngaysinh;
                    sqlcmd.Parameters.Add("@soCMND", SqlDbType.NVarChar, 15).Value = item.soCMND;
                    sqlcmd.Parameters.Add("@soDT", SqlDbType.NVarChar, 25).Value = item.soDT;
                    sqlcmd.Parameters.Add("@diachi", SqlDbType.NVarChar, 250).Value = item.diachi;
                    sqlcmd.Parameters.Add("@idTP", SqlDbType.SmallInt).Value = item.idTP;
                    sqlcmd.Parameters.Add("@TenTP", SqlDbType.NVarChar, 75).Value = item.TenTP;
                    sqlcmd.Parameters.Add("@idQH", SqlDbType.SmallInt).Value = item.idQH;
                    sqlcmd.Parameters.Add("@tenQH", SqlDbType.NVarChar, 75).Value = item.tenQH;
                    sqlcmd.Parameters.Add("@idPX", SqlDbType.SmallInt).Value = item.idPX;
                    sqlcmd.Parameters.Add("@tenPX", SqlDbType.NVarChar, 75).Value = item.tenPX;
                    sqlcmd.Parameters.Add("@idTo", SqlDbType.SmallInt).Value = item.idTo;
                    sqlcmd.Parameters.Add("@tenTo", SqlDbType.NVarChar, 75).Value = item.tenTo;
                    sqlcmd.Parameters.Add("@idDuong", SqlDbType.SmallInt).Value = item.idDuong;
                    sqlcmd.Parameters.Add("@tenDuong", SqlDbType.NVarChar, 150).Value = item.tenDuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = item.idPho;
                    sqlcmd.Parameters.Add("@tenPho", SqlDbType.NVarChar, 150).Value = item.tenPho;
                    sqlcmd.Parameters.Add("@loaiKH", SqlDbType.SmallInt).Value = item.loaiKH;
                    sqlcmd.Parameters.Add("@tenLKH", SqlDbType.NVarChar, 150).Value = item.tenLKH;
                    sqlcmd.Parameters.Add("@idMDSD", SqlDbType.SmallInt).Value = item.idMDSD;
                    sqlcmd.Parameters.Add("@tenMDSD", SqlDbType.NVarChar, 150).Value = item.tenMDSD;
                    sqlcmd.Parameters.Add("@sohoSD", SqlDbType.SmallInt).Value = item.sohoSD;
                    sqlcmd.Parameters.Add("@sokhauSD", SqlDbType.SmallInt).Value = item.sokhauSD;
                    sqlcmd.Parameters.Add("@ngayDK", SqlDbType.BigInt).Value = item.ngayDK;
                    sqlcmd.Parameters.Add("@trangthai", SqlDbType.SmallInt).Value = item.trangthai;
                    sqlcmd.Parameters.Add("@klttdukien", SqlDbType.Int).Value = item.KLTTdukien;
                    sqlcmd.Parameters.Add("@motakh", SqlDbType.NVarChar, 4000).Value = item.mota;
                    sqlcmd.Parameters.Add("@masothue", SqlDbType.NVarChar, 25).Value = item.maSoThue;

                    sqlcmd.ExecuteNonQuery();
                    sqlcmd.Dispose();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public int kihopdong(Int64 id,string soHD,Int16 idKM,string tenKM,Int16 phi,Int16 idgia,Int64 ngayKHD,Int16 trangthai)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_kihopdong", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id;
                    sqlcmd.Parameters.Add("@soHD", SqlDbType.NVarChar,25).Value = soHD;
                    sqlcmd.Parameters.Add("@idKM",SqlDbType.SmallInt).Value = idKM;
	                sqlcmd.Parameters.Add("@tenKM",SqlDbType.NVarChar,150).Value = tenKM;
	                sqlcmd.Parameters.Add("@phi",SqlDbType.SmallInt).Value = phi;
                    sqlcmd.Parameters.Add("@idgia", SqlDbType.SmallInt).Value = idgia;
                    sqlcmd.Parameters.Add("@ngayKHD", SqlDbType.BigInt).Value = ngayKHD;
                    sqlcmd.Parameters.Add("@trangthai", SqlDbType.SmallInt).Value = trangthai;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Close(); }
            return rtn;
        }

        public int thicong(Int64 idkh, string madh, Int16 idldh, string kichco, Int16 idtolap, string tolap, Int64 ngaylap, Int16 iddhtong, string tendhtong, string dutoan,Nullable<float> tongdt,Nullable<int> chisoDH, Int16 trangthai, string motalapdat,Int16 matuyen, string tentuyen)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_thicong", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;  // bigint,
                    sqlcmd.Parameters.Add("@madh", SqlDbType.NVarChar, 25).Value = madh;  // nvarchar(25),
                    sqlcmd.Parameters.Add("@idldh", SqlDbType.SmallInt).Value = idldh;  // smallint,
                    sqlcmd.Parameters.Add("@kichco", SqlDbType.NVarChar, 50).Value = kichco;  // nvarchar(50),
                    sqlcmd.Parameters.Add("@idtolap", SqlDbType.SmallInt).Value = idtolap;
                    sqlcmd.Parameters.Add("@tolap", SqlDbType.NVarChar, 250).Value = tolap;  // nvarchar(250),	-- to lap dat dong ho
                    sqlcmd.Parameters.Add("@ngaylap", SqlDbType.BigInt).Value = ngaylap;  // bigint,		-- ngay lap dat
                    sqlcmd.Parameters.Add("@iddhtong", SqlDbType.SmallInt).Value = iddhtong;  // smallint,		-- dong ho tong
                    sqlcmd.Parameters.Add("@tendhtong", SqlDbType.NVarChar, 150).Value = tendhtong;  // nvarchar(150),
                    //sqlcmd.Parameters.Add("@khtong", SqlDbType.NVarChar, 25).Value = khtong;  // nvarchar(25),	-- ma khach hang dong ho lap truoc
                    sqlcmd.Parameters.Add("@dutoan", SqlDbType.NVarChar).Value = dutoan;  // text,
                    sqlcmd.Parameters.Add("@tongdt", SqlDbType.Float).Value = tongdt;  // float,
                    //sqlcmd.Parameters.Add("@mavitri", SqlDbType.NVarChar, 25).Value = mavitri;  // nvarchar(25),	-- ma khach hang truoc sau
                    //sqlcmd.Parameters.Add("@type", SqlDbType.SmallInt).Value = type;  // smallint,			-- 1 truoc, 0 la sau
                    sqlcmd.Parameters.Add("@chiSoDH", SqlDbType.Int).Value = chisoDH;
                    sqlcmd.Parameters.Add("@trangthai", SqlDbType.SmallInt).Value = trangthai;  // smallint
                    sqlcmd.Parameters.Add("@mota", SqlDbType.NVarChar, 250).Value = motalapdat;
                    sqlcmd.Parameters.Add("@matuyen", SqlDbType.SmallInt).Value = matuyen;
                    sqlcmd.Parameters.Add("@tentuyen", SqlDbType.NVarChar, 75).Value = tentuyen;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public DataTable getautocomplete(Int16 idduong, Int16 iddhtong)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_autocomplete", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idduong", SqlDbType.SmallInt).Value = idduong;
                    sqlcmd.Parameters.Add("@iddhtong", SqlDbType.SmallInt).Value = iddhtong;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int thaycumdh(Int64 id,Int16 iddhtong,string tendhtong,Int16 trangthai)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_thaydoicumdh", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id;
                    //sqlcmd.Parameters.Add("@idtolap", SqlDbType.SmallInt).Value = idtolap;
                    //sqlcmd.Parameters.Add("@tolap", SqlDbType.NVarChar, 250).Value = tolap;  //-- to lap dat dong ho
                    sqlcmd.Parameters.Add("@iddhtong", SqlDbType.SmallInt).Value = iddhtong;		//-- dong ho tong
                    sqlcmd.Parameters.Add("@tendhtong", SqlDbType.NVarChar, 150).Value = tendhtong;
                    sqlcmd.Parameters.Add("@trangthai", SqlDbType.SmallInt).Value = trangthai;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public int capnhatkhachhang(Int64 id,itkhachhang item)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_capnhat", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;

                    sqlcmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id;
                    sqlcmd.Parameters.Add("@makh", SqlDbType.NVarChar, 25).Value = item.maCu;
                    sqlcmd.Parameters.Add("@tenKH", SqlDbType.NVarChar, 150).Value = item.tenKH;
                    sqlcmd.Parameters.Add("@tenTK", SqlDbType.NVarChar, 150).Value = item.tenTK;
                    sqlcmd.Parameters.Add("@ngaysinh", SqlDbType.NVarChar, 50).Value = item.ngaysinh;
                    sqlcmd.Parameters.Add("@soCMND", SqlDbType.NVarChar, 15).Value = item.soCMND;
                    sqlcmd.Parameters.Add("@soDT", SqlDbType.NVarChar, 25).Value = item.soDT;
                    sqlcmd.Parameters.Add("@diachi", SqlDbType.NVarChar, 250).Value = item.diachi;
                    sqlcmd.Parameters.Add("@idTP", SqlDbType.SmallInt).Value = item.idTP;
                    sqlcmd.Parameters.Add("@TenTP", SqlDbType.NVarChar, 75).Value = item.TenTP;
                    sqlcmd.Parameters.Add("@idQH", SqlDbType.SmallInt).Value = item.idQH;
                    sqlcmd.Parameters.Add("@tenQH", SqlDbType.NVarChar, 75).Value = item.tenQH;
                    sqlcmd.Parameters.Add("@idPX", SqlDbType.SmallInt).Value = item.idPX;
                    sqlcmd.Parameters.Add("@tenPX", SqlDbType.NVarChar, 75).Value = item.tenPX;
                    sqlcmd.Parameters.Add("@idTo", SqlDbType.SmallInt).Value = item.idTo;
                    sqlcmd.Parameters.Add("@tenTo", SqlDbType.NVarChar, 75).Value = item.tenTo;
                    sqlcmd.Parameters.Add("@idDuong", SqlDbType.SmallInt).Value = item.idDuong;
                    sqlcmd.Parameters.Add("@tenDuong", SqlDbType.NVarChar, 150).Value = item.tenDuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = item.idPho;
                    sqlcmd.Parameters.Add("@tenPho", SqlDbType.NVarChar, 150).Value = item.tenPho;
                    sqlcmd.Parameters.Add("@loaiKH", SqlDbType.SmallInt).Value = item.loaiKH;
                    sqlcmd.Parameters.Add("@tenLKH", SqlDbType.NVarChar, 150).Value = item.tenLKH;
                    sqlcmd.Parameters.Add("@idMDSD", SqlDbType.SmallInt).Value = item.idMDSD;
                    sqlcmd.Parameters.Add("@tenMDSD", SqlDbType.NVarChar, 150).Value = item.tenMDSD;
                    sqlcmd.Parameters.Add("@sohoSD", SqlDbType.SmallInt).Value = item.sohoSD;
                    sqlcmd.Parameters.Add("@sokhauSD", SqlDbType.SmallInt).Value = item.sokhauSD;
                    sqlcmd.Parameters.Add("@ngayDK", SqlDbType.BigInt).Value = item.ngayDK;
                    sqlcmd.Parameters.Add("@trangthai", SqlDbType.SmallInt).Value = item.trangthai;
                    sqlcmd.Parameters.Add("@klttdukien", SqlDbType.Int).Value = item.KLTTdukien;
                    sqlcmd.Parameters.Add("@motakh", SqlDbType.NVarChar, 4000).Value = item.mota;
                    sqlcmd.Parameters.Add("@masothue", SqlDbType.NVarChar, 25).Value = item.maSoThue;

                    sqlcmd.ExecuteNonQuery();
                    sqlcmd.Dispose();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public DataTable getvipham()
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_vipham", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int khoansudung(Int64 id, Int16 khoi)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_khoan", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id;
                    sqlcmd.Parameters.Add("@khoan", SqlDbType.SmallInt).Value = khoi;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public int countrowkh(string type, Int16 id, Int16 id2)
        {
            int kh = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_countrow_kh", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@type", SqlDbType.NVarChar, 10).Value = type;
                    sqlcmd.Parameters.Add("@id1", SqlDbType.SmallInt).Value = id;
                    sqlcmd.Parameters.Add("@id2", SqlDbType.SmallInt).Value = id2;
                    kh = Convert.ToInt32(sqlcmd.ExecuteScalar());
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return kh;
        }

        public DataTable loadkh(int page, int sodong,string type,Int16 id,Int16 id2)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_load_phantrang", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@page", SqlDbType.Int).Value = page;
                    sqlcmd.Parameters.Add("@countrecord", SqlDbType.Int).Value = sodong;
                    sqlcmd.Parameters.Add("@type", SqlDbType.NVarChar, 10).Value = type;
                    sqlcmd.Parameters.Add("@id1", SqlDbType.SmallInt).Value = id;
                    sqlcmd.Parameters.Add("@id2", SqlDbType.SmallInt).Value = id2;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int updatevitrikh(Int64 idkh, int vitri)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_khachhang_addvitri", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@vitri", SqlDbType.Int).Value = vitri;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public int chuyenViTri(Int64 idkh, int vitrimoi)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_KhachHang_chuyenViTri", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@vitrimoi", SqlDbType.Int).Value = vitrimoi;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }
    }

    public class itkhachhang
    { 
        public Int64 id { get; set; }
	    public string maCu { get; set; }
	    public string soHD { get; set; }
	    public string tenKH { get; set; }
	    public string tenTK { get; set; }
	    public string ngaysinh { get; set; }
	    public string soCMND { get; set; }
	    public string soDT { get; set; }
	    public string diachi { get; set; }
	    public Int16 idTuyen { get; set; }
	    public string TenTuyen { get; set; }
	    public Int16 idTP { get; set; }
	    public string TenTP { get; set; }
	    public Int16 idQH { get; set; }
	    public string tenQH { get; set; }
	    public Int16 idPX { get; set; }
	    public string tenPX { get; set; }
	    public Int16 idTo { get; set; }
	    public string tenTo { get; set; }
	    public Int16 idDuong { get; set; }
	    public string tenDuong { get; set; }
        public Int16 idPho { get; set; }
        public string tenPho { get; set; }
	    public Int16 idDHTong { get; set; }
	    public string tenDHTong { get; set; }
	    public Int16 idCoQuan { get; set; }
	    public string tenCoQuan { get; set; }
	    public Int16 loaiKH { get; set; }
	    public string tenLKH { get; set; }
	    public Int16 idMDSD { get; set; }
	    public string tenMDSD { get; set; }
	    public Nullable<Int16> sohoSD { get; set; }
	    public Nullable<Int16> sokhauSD { get; set; }
        public Int64 ngayDK { get; set; }
	    public string dutoan { get; set; }
	    public string tongdutoan { get; set; }
	    public Int64 ngayKHD { get; set; }
        public Int64 ngayTC { get; set; }
	    public Int16 donviTC { get; set; }
	    public bool phi { get; set; }
	    public Int16 idKM { get; set; }
	    public string tenKM { get; set; }
	    public Int16 khoan { get; set; }
	    public Int16 idgia { get; set; }
	    public Int64 idKHC { get; set; }
	    public Int64 vitri { get; set; }
	    public Int16 idCTy { get; set; }
	    public Int16 idNMay { get; set; }
        public Int16 trangthai { get; set; }
        public Nullable<int> KLTTdukien{ get; set; }
        public string mota { get; set; }
        public string maSoThue { get; set; }
    }
}
